package wang.xinqiu.mysqlcontrol.data

data class SQLSuggestion(
    val text: String,
    val type: String,
    val description: String = "",
    val relevance: Float = 0.5f
)

enum class QueryContext {
    AFTER_SELECT, AFTER_FROM, AFTER_WHERE, AFTER_JOIN, GENERAL
}

class SmartSQLHelper {
    private val sqlKeywords = listOf(
        "SELECT", "FROM", "WHERE", "INSERT", "UPDATE", "DELETE", 
        "CREATE", "DROP", "ALTER", "INDEX", "TABLE", "DATABASE",
        "ORDER BY", "GROUP BY", "HAVING", "LIMIT", "OFFSET",
        "JOIN", "LEFT JOIN", "RIGHT JOIN", "INNER JOIN", "OUTER JOIN",
        "UNION", "DISTINCT", "COUNT", "SUM", "AVG", "MAX", "MIN"
    )
    
    private val sqlFunctions = listOf(
        "COUNT(*)", "COUNT(DISTINCT ", "SUM(", "AVG(", "MAX(", "MIN(",
        "NOW()", "CURDATE()", "CURTIME()", "DATE(", "YEAR(", "MONTH(", "DAY(",
        "CONCAT(", "SUBSTRING(", "LENGTH(", "UPPER(", "LOWER(", "TRIM("
    )
    
    private val commonColumns = listOf(
        "id", "name", "title", "description", "email", "phone", "address",
        "created_at", "updated_at", "status", "type", "category", "price", "amount"
    )
    
    fun getContextualSuggestions(
        query: String, 
        cursorPosition: Int, 
        tables: List<String>, 
        tableColumns: Map<String, List<String>>,
        queryHistory: List<String>
    ): List<SQLSuggestion> {
        val suggestions = mutableListOf<SQLSuggestion>()
        val beforeCursor = query.substring(0, cursorPosition).uppercase()
        val currentWord = getCurrentWord(query, cursorPosition)
        val context = analyzeQueryContext(beforeCursor)
        val tablesInQuery = extractTablesFromQuery(query)
        
        // 1. 基于上下文的智能建议
        when (context) {
            QueryContext.AFTER_SELECT -> {
                suggestions.addAll(getColumnSuggestions(currentWord, tablesInQuery, tableColumns))
                suggestions.addAll(getFunctionSuggestions(currentWord))
                if (currentWord.isEmpty()) {
                    suggestions.add(SQLSuggestion("*", "wildcard", "选择所有列", 0.9f))
                }
            }
            QueryContext.AFTER_FROM -> {
                suggestions.addAll(getTableSuggestions(currentWord, tables))
            }
            QueryContext.AFTER_WHERE -> {
                suggestions.addAll(getColumnSuggestions(currentWord, tablesInQuery, tableColumns))
                suggestions.addAll(getOperatorSuggestions(currentWord))
                suggestions.addAll(getWhereConditionSuggestions(currentWord, tablesInQuery, tableColumns))
                if (currentWord.isEmpty() || currentWord.length < 2) {
                    suggestions.addAll(getWhereTemplateSuggestions())
                }
            }
            QueryContext.AFTER_JOIN -> {
                suggestions.addAll(getTableSuggestions(currentWord, tables))
            }
            QueryContext.GENERAL -> {
                suggestions.addAll(getKeywordSuggestions(currentWord))
                suggestions.addAll(getTemplateSuggestions(currentWord))
            }
        }
        
        // 2. 查询历史建议
        suggestions.addAll(getHistorySuggestions(query, queryHistory))
        
        // 3. 智能片段建议
        suggestions.addAll(getSnippetSuggestions(currentWord, context))
        
        return suggestions
            .distinctBy { it.text }
            .sortedByDescending { it.relevance }
            .take(15)
    }
    
    private fun getCurrentWord(query: String, cursorPosition: Int): String {
        val start = query.lastIndexOf(' ', cursorPosition - 1) + 1
        val end = query.indexOf(' ', cursorPosition).let { if (it == -1) query.length else it }
        return if (start < end && start >= 0) query.substring(start, minOf(end, cursorPosition)) else ""
    }
    
    private fun analyzeQueryContext(beforeCursor: String): QueryContext {
        val trimmed = beforeCursor.trim()
        return when {
            // 检查最近的关键字
            trimmed.endsWith("WHERE") || trimmed.contains("WHERE") && !trimmed.substringAfterLast("WHERE").contains("ORDER") -> QueryContext.AFTER_WHERE
            trimmed.endsWith("SELECT") || (trimmed.contains("SELECT") && !trimmed.contains("FROM")) -> QueryContext.AFTER_SELECT
            trimmed.endsWith("FROM") || (trimmed.contains("FROM") && !trimmed.contains("WHERE") && !trimmed.contains("JOIN")) -> QueryContext.AFTER_FROM
            trimmed.contains("JOIN") && !trimmed.substringAfterLast("JOIN").contains("ON") -> QueryContext.AFTER_JOIN
            else -> QueryContext.GENERAL
        }
    }
    
    private fun extractTablesFromQuery(query: String): List<String> {
        val upperQuery = query.uppercase()
        val tables = mutableListOf<String>()
        
        // 提取 FROM 后的表名
        val fromPattern = Regex("FROM\\s+`?([a-zA-Z_][a-zA-Z0-9_]*)`?", RegexOption.IGNORE_CASE)
        fromPattern.findAll(query).forEach { match ->
            tables.add(match.groupValues[1])
        }
        
        // 提取 JOIN 后的表名
        val joinPattern = Regex("JOIN\\s+`?([a-zA-Z_][a-zA-Z0-9_]*)`?", RegexOption.IGNORE_CASE)
        joinPattern.findAll(query).forEach { match ->
            tables.add(match.groupValues[1])
        }
        
        return tables.distinct()
    }
    
    private fun getColumnSuggestions(
        currentWord: String, 
        tablesInQuery: List<String>, 
        tableColumns: Map<String, List<String>>
    ): List<SQLSuggestion> {
        val suggestions = mutableListOf<SQLSuggestion>()
        
        // 如果查询中有表，使用实际的列名
        if (tablesInQuery.isNotEmpty()) {
            tablesInQuery.forEach { tableName ->
                tableColumns[tableName]?.forEach { column ->
                    if (column.contains(currentWord, ignoreCase = true)) {
                        suggestions.add(SQLSuggestion(column, "column", "表 $tableName 的列", 0.9f))
                    }
                }
            }
        } else {
            // 如果没有表信息，使用通用列名
            commonColumns
                .filter { it.contains(currentWord, ignoreCase = true) }
                .forEach { column ->
                    suggestions.add(SQLSuggestion(column, "column", "常用列名", 0.6f))
                }
        }
        
        return suggestions
    }
    
    private fun getFunctionSuggestions(currentWord: String): List<SQLSuggestion> {
        return sqlFunctions
            .filter { it.contains(currentWord, ignoreCase = true) }
            .map { SQLSuggestion(it, "function", "SQL函数", 0.7f) }
    }
    
    private fun getTableSuggestions(currentWord: String, tables: List<String>): List<SQLSuggestion> {
        return tables
            .filter { it.contains(currentWord, ignoreCase = true) }
            .map { SQLSuggestion("`$it`", "table", "表名", 0.9f) }
    }
    
    private fun getOperatorSuggestions(currentWord: String): List<SQLSuggestion> {
        val operators = listOf(
            "= " to "等于",
            "!= " to "不等于", 
            "LIKE '%'" to "模糊匹配",
            "IN ()" to "包含于",
            "BETWEEN " to "范围查询",
            "IS NULL" to "为空",
            "IS NOT NULL" to "不为空",
            "> " to "大于",
            "< " to "小于",
            ">= " to "大于等于",
            "<= " to "小于等于"
        )
        
        return operators
            .filter { it.first.contains(currentWord, ignoreCase = true) }
            .map { SQLSuggestion(it.first, "operator", it.second, 0.6f) }
    }
    
    private fun getKeywordSuggestions(currentWord: String): List<SQLSuggestion> {
        return sqlKeywords
            .filter { it.startsWith(currentWord.uppercase()) }
            .map { SQLSuggestion(it, "keyword", "SQL关键字", 0.8f) }
    }
    
    private fun getTemplateSuggestions(currentWord: String): List<SQLSuggestion> {
        val templates = mapOf(
            "select" to "SELECT * FROM table_name WHERE condition",
            "insert" to "INSERT INTO table_name (column1, column2) VALUES (value1, value2)",
            "update" to "UPDATE table_name SET column1 = value1 WHERE condition",
            "delete" to "DELETE FROM table_name WHERE condition",
            "create" to "CREATE TABLE table_name (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))"
        )
        
        return templates
            .filter { it.key.contains(currentWord, ignoreCase = true) }
            .map { SQLSuggestion(it.value, "template", "SQL模板", 0.9f) }
    }
    
    private fun getHistorySuggestions(query: String, queryHistory: List<String>): List<SQLSuggestion> {
        if (query.length < 3) return emptyList()
        
        return queryHistory
            .filter { it.contains(query, ignoreCase = true) && it != query }
            .take(3)
            .map { SQLSuggestion(it, "history", "历史查询", 0.9f) }
    }
    
    private fun getSnippetSuggestions(currentWord: String, context: QueryContext): List<SQLSuggestion> {
        val snippets = mutableListOf<SQLSuggestion>()
        
        when (context) {
            QueryContext.AFTER_WHERE -> {
                if ("order".contains(currentWord, ignoreCase = true)) {
                    snippets.add(SQLSuggestion("ORDER BY column_name ASC", "snippet", "排序", 0.7f))
                    snippets.add(SQLSuggestion("ORDER BY column_name DESC", "snippet", "倒序", 0.7f))
                }
                if ("group".contains(currentWord, ignoreCase = true)) {
                    snippets.add(SQLSuggestion("GROUP BY column_name", "snippet", "分组", 0.7f))
                }
                if ("limit".contains(currentWord, ignoreCase = true)) {
                    snippets.add(SQLSuggestion("LIMIT 10", "snippet", "限制10条", 0.7f))
                    snippets.add(SQLSuggestion("LIMIT 100", "snippet", "限制100条", 0.6f))
                }
            }
            QueryContext.AFTER_FROM -> {
                if ("where".contains(currentWord, ignoreCase = true)) {
                    snippets.add(SQLSuggestion("WHERE column_name = 'value'", "snippet", "条件查询", 0.8f))
                }
                if ("join".contains(currentWord, ignoreCase = true)) {
                    snippets.add(SQLSuggestion("LEFT JOIN table2 ON table1.id = table2.table1_id", "snippet", "左连接", 0.7f))
                }
            }
            else -> {}
        }
        
        return snippets
    }
    private fun getWhereConditionSuggestions(
        currentWord: String,
        tablesInQuery: List<String>,
        tableColumns: Map<String, List<String>>
    ): List<SQLSuggestion> {
        val suggestions = mutableListOf<SQLSuggestion>()
        
        // 基于实际表列生成条件建议
        tablesInQuery.forEach { tableName ->
            tableColumns[tableName]?.forEach { column ->
                if (column.contains(currentWord, ignoreCase = true)) {
                    suggestions.addAll(listOf(
                        SQLSuggestion("$column = ", "condition", "$column 等于", 0.8f),
                        SQLSuggestion("$column LIKE '%", "condition", "$column 模糊匹配", 0.7f),
                        SQLSuggestion("$column IS NOT NULL", "condition", "$column 非空", 0.6f)
                    ))
                }
            }
        }
        
        return suggestions
    }
    
    private fun getWhereTemplateSuggestions(): List<SQLSuggestion> {
        return listOf(
            SQLSuggestion("id = 1", "template", "ID条件", 0.9f),
            SQLSuggestion("name = 'value'", "template", "字符串条件", 0.8f),
            SQLSuggestion("status IN (1, 2)", "template", "多值条件", 0.7f),
            SQLSuggestion("created_at > NOW()", "template", "时间条件", 0.7f),
            SQLSuggestion("column IS NOT NULL", "template", "非空条件", 0.6f)
        )
    }
}
